﻿using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Enums;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PMAF.K3Cloud.SCM.Report.ServicePlugIn
{
    [Kingdee.BOS.Util.HotUpdate]
    [Description("采购员业绩报表服务插件")]
    public class PurchaserAchievementReport : SysReportBaseService
    {
        public override void Initialize()
        {
            //初始化
            base.Initialize();
            //简单账表类型：普通、树形、分页
            ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            //报表显示顺序字段
            //ReportProperty.IdentityFieldName = "FIDENTITYID";
            //是用插件创建临时表
            IsCreateTempTableByPlugin = true;
            //是否分组汇总
            ReportProperty.IsGroupSummary = true;
        }

        public override ReportTitles GetReportTitles(IRptParams filter)
        {
            base.GetReportTitles(filter);
            ReportTitles reportTitles = new ReportTitles();
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            if (customFilter != null)
            {
                object startDate = customFilter["F_PMAF_StartDate"];
                object endDate = customFilter["F_PMAF_EndDate"];
                string dateZone = GetDateZone(startDate, endDate);
                reportTitles.AddTitle("F_PMAF_DateZone", dateZone);
                DynamicObjectCollection purchasers = (DynamicObjectCollection)customFilter["F_PMAF_Purchasers"];
                string purchasersStr = GetPurchasers(purchasers);
                reportTitles.AddTitle("F_PMAF_Purchasers", purchasersStr);
                DynamicObject startMaterial = (DynamicObject)customFilter["F_PMAF_StartMaterialId"];
                DynamicObject endMaterial = (DynamicObject)customFilter["F_PMAF_EndMaterialId"];
            }
            return reportTitles;
        }

        /// <summary>
        /// 设置报表列
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            ReportHeader header = new ReportHeader();
            header.AddChild("FBILLNO", new LocaleValue("采购订单", Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FDATE", new LocaleValue("采购日期", Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("FDELIVERYDATE", new LocaleValue("要货日期", Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("PURCHASER", new LocaleValue("采购员", Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("MATERIALNUMBER", new LocaleValue("商品编码", Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("MATERIALNAME", new LocaleValue("商品名称", Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("CONTRACTTYPE", new LocaleValue("合同类型", Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("PURCHASESN", new LocaleValue("采购流水号", Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
            header.AddChild("PURQTY", new LocaleValue("采购数量", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FTAXPRICE", new LocaleValue("采购单价", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("FALLAMOUNT", new LocaleValue("采购金额", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("SALESELFQTY", new LocaleValue("自销数量", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("SALESELFAMOUNT", new LocaleValue("自销金额", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("SALESELFPROFIT", new LocaleValue("自销利润", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("SALEOTHERQTY", new LocaleValue("他销数量", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("ADDAMOUNT", new LocaleValue("他销加价", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("SALEOTHERAMOUNT", new LocaleValue("他销金额", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("SALEOTHERADDPROFIT", new LocaleValue("他销内部利润", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("SALEOTHERPROFIT", new LocaleValue("他销利润", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("UNSALEQTY", new LocaleValue("未销数量", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("UNSALEAMOUNT", new LocaleValue("未销金额", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("OUTSTOCKQTY", new LocaleValue("提货数量", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("CUSTCOMMISSION", new LocaleValue("客户销售费用", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("SALECOMMISSION", new LocaleValue("销售员提成", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("TRANAMOUNT", new LocaleValue("运费", Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
            header.AddChild("AVGPURSALCYCLE", new LocaleValue("采销周期", Context.UserLocale.LCID), SqlStorageType.SqlInt);
            return header;
        }

        /// <summary>
        /// SQL语句构建报表数据
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="tableName"></param>
        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            string sqlStr = string.Format(@"/*dialect*/
            SELECT
            t0.FID,
            t0.FENTRYID,
            t1.FBILLNO,
            t1.FDATE,
            t1.FPURCHASERID,
            t6.FNAME AS PURCHASER,
            t9.SALENAME,
            t9.FQTY AS SALEOTHERQTY,
            t9.ADDAMOUNT,
            t9.FALLAMOUNT AS SALEOTHERAMOUNT
            INTO #TEMP0
            FROM T_PUR_POORDERENTRY t0
            LEFT JOIN T_PUR_POORDER t1 ON t1.FID=t0.FID
            LEFT JOIN V_BD_BUYER_L t6 ON t6.FID=t1.FPURCHASERID
            LEFT JOIN (
            SELECT
            SUM(t0.FQTY) AS FQTY,
            SUM(t0.FQTY*t0.F_PMAF_ACHIEVEMENTPRICE) AS ADDAMOUNT,
            SUM(t2.FALLAMOUNT) AS FALLAMOUNT,
            t3.FNAME AS SALENAME,
            t0.F_PMAF_PURCHASESN AS PURCHASESN
            FROM T_SAL_ORDERENTRY t0
            LEFT JOIN T_SAL_ORDER t1 ON t1.FID=t0.FID
            LEFT JOIN T_SAL_ORDERENTRY_F t2 ON t2.FID=t0.FID AND t2.FENTRYID=t0.FENTRYID
            LEFT JOIN V_BD_SALESMAN_L t3 ON t3.fid=t1.FSALERID
            GROUP BY t0.F_PMAF_PURCHASESN,t3.FNAME) t9 ON t9.PURCHASESN=t0.F_PMAF_PURCHASESNID AND t9.SALENAME<>t6.FNAME;
            SELECT
            t0.FID,
            t0.FENTRYID,
            t1.FBILLNO,
            t1.FDATE,
            t1.FPURCHASERID,
            t6.FNAME AS PURCHASER,
            t2.FDELIVERYDATE,
            t0.FMATERIALID,
            t4.FNUMBER AS MATERIALNUMBER,
            t5.FNAME AS MATERIALNAME,
            t0.F_PMAF_CONTRACTTYPE AS CONTRACTTYPE,
            t0.FQTY,
            t3.FTAXPRICE,
            t3.FALLAMOUNT,
            t0.F_PMAF_PURCHASESNID,
            t7.FNUMBER AS PURCHASESN,
            t8.FQTY AS SALESELFQTY,
            t8.FALLAMOUNT AS SALESELFAMOUNT,
            t9.SALEOTHERQTY,
            t9.ADDAMOUNT,
            t9.SALEOTHERAMOUNT
            INTO #TEMP1
            FROM T_PUR_POORDERENTRY t0
            LEFT JOIN T_PUR_POORDER t1 ON t1.FID=t0.FID
            LEFT JOIN T_PUR_POORDERENTRY_D t2 ON t2.FID=t0.FID AND t2.FENTRYID=t0.FENTRYID
            LEFT JOIN T_PUR_POORDERENTRY_F t3 ON t3.FID=t0.FID AND t3.FENTRYID=t0.FENTRYID
            LEFT JOIN T_BD_MATERIAL t4 ON t4.FMATERIALID=t0.FMATERIALID
            LEFT JOIN T_BD_MATERIAL_L t5 ON t5.FMATERIALID=t0.FMATERIALID
            LEFT JOIN V_BD_BUYER_L t6 ON t6.FID=t1.FPURCHASERID
            LEFT JOIN PMAF_t_PurchaseSn t7 ON t7.FID=t0.F_PMAF_PURCHASESNID
            LEFT JOIN (
            SELECT
            SUM(t0.FQTY) AS FQTY,
            SUM(t2.FALLAMOUNT) AS FALLAMOUNT,
            t3.FNAME AS SALENAME,
            t0.F_PMAF_PURCHASESN AS PURCHASESN
            FROM T_SAL_ORDERENTRY t0
            LEFT JOIN T_SAL_ORDER t1 ON t1.FID=t0.FID
            LEFT JOIN T_SAL_ORDERENTRY_F t2 ON t2.FID=t0.FID AND t2.FENTRYID=t0.FENTRYID
            LEFT JOIN V_BD_SALESMAN_L t3 ON t3.fid=t1.FSALERID
            GROUP BY t0.F_PMAF_PURCHASESN,t3.FNAME) t8 ON t8.PURCHASESN=t0.F_PMAF_PURCHASESNID AND t8.SALENAME=t6.FNAME
            LEFT JOIN (
            SELECT
            FID,
            FENTRYID,
            FBILLNO,
            FPURCHASERID,
            SUM(SALEOTHERQTY) AS SALEOTHERQTY,
            SUM(ADDAMOUNT) AS ADDAMOUNT,
            SUM(SALEOTHERAMOUNT) AS SALEOTHERAMOUNT
            FROM #TEMP0
            GROUP BY FID,FENTRYID,FBILLNO,FPURCHASERID) t9 ON t9.FID=t0.FID AND t9.FENTRYID=t0.FENTRYID;
            UPDATE #TEMP1 SET SALESELFQTY=0 WHERE SALESELFQTY IS NULL;
            UPDATE #TEMP1 SET SALESELFAMOUNT=0 WHERE SALESELFAMOUNT IS NULL;
            UPDATE #TEMP1 SET SALEOTHERQTY=0 WHERE SALEOTHERQTY IS NULL;
            UPDATE #TEMP1 SET ADDAMOUNT=0 WHERE ADDAMOUNT IS NULL;
            UPDATE #TEMP1 SET SALEOTHERAMOUNT=0 WHERE SALEOTHERAMOUNT IS NULL;
            SELECT
            ROW_NUMBER() OVER(ORDER BY e0.PURCHASER,e0.FDATE) AS FIDENTITYID,
            e0.FBILLNO,
            CONVERT(nvarchar(10),e0.FDATE,23) AS FDATE,
            CONVERT(nvarchar(10),e0.FDELIVERYDATE,23) AS FDELIVERYDATE,
            e0.PURCHASER,
            e0.MATERIALNUMBER,
            e0.MATERIALNAME,
            CASE
	            WHEN e0.CONTRACTTYPE=1 THEN '基差合同'
	            WHEN e0.CONTRACTTYPE=2 THEN '点价合同'
	            WHEN e0.CONTRACTTYPE=3 THEN '现货合同'
            END AS CONTRACTTYPE,
            e0.FPURCHASERID,
            e0.PURCHASESN,
            e0.FQTY AS PURQTY,
            e0.FTAXPRICE,
            e0.FALLAMOUNT,
            e0.SALESELFQTY,
            e0.SALESELFAMOUNT,
            e0.SALESELFAMOUNT-e0.SALESELFQTY*e0.FTAXPRICE AS SALESELFPROFIT,
            e0.SALEOTHERQTY,
            e0.ADDAMOUNT,
            e0.SALEOTHERAMOUNT,
            e0.ADDAMOUNT AS SALEOTHERADDPROFIT,
            e0.SALEOTHERAMOUNT-e0.ADDAMOUNT-e0.SALEOTHERQTY*e0.FTAXPRICE AS SALEOTHERPROFIT,
            e0.FQTY-e0.SALESELFQTY-e0.SALEOTHERQTY AS UNSALEQTY,
            (e0.FQTY-e0.SALESELFQTY-e0.SALEOTHERQTY)*e0.FTAXPRICE AS UNSALEAMOUNT,
            e2.OUTSTOCKQTY,
            e2.CUSTCOMMISSION,
            e2.SALECOMMISSION,
            e2.TRANAMOUNT,
            e1.AVGPURSALCYCLE
            INTO {0}
            FROM #TEMP1 e0
            LEFT JOIN (
            SELECT
            F_PMAF_PURCHASESN,
            SUM(AVGPURSALCYCLE) AS AVGPURSALCYCLE
            FROM(
            SELECT
            t1.FDATE AS SALEDATE,
            t0.FQTY,
            t4.FDATE AS PURDATE,
            DATEDIFF(day,t4.FDATE,t1.FDATE) AS PURSALCYCLE,
            t0.FQTY*t0.F_PMAF_ACHIEVEMENTPRICE AS ADDAMOUNT,
            t2.FALLAMOUNT,
            t5.SUMQTY,
            t0.FQTY/t5.SUMQTY*DATEDIFF(day,t4.FDATE,t1.FDATE) AS AVGPURSALCYCLE,
            t0.F_PMAF_PURCHASESN
            FROM T_SAL_ORDERENTRY t0
            LEFT JOIN T_SAL_ORDER t1 ON t1.FID=t0.FID
            LEFT JOIN T_SAL_ORDERENTRY_F t2 ON t2.FID=t0.FID AND t2.FENTRYID=t0.FENTRYID
            LEFT JOIN T_PUR_POORDERENTRY t3 ON t3.F_PMAF_PURCHASESNID=t0.F_PMAF_PURCHASESN
            LEFT JOIN T_PUR_POORDER t4 ON t4.FID=t3.FID
            LEFT JOIN (
            SELECT
            SUM(FQTY) AS SUMQTY,
            F_PMAF_PURCHASESN
            FROM T_SAL_ORDERENTRY
            GROUP BY F_PMAF_PURCHASESN) t5 ON t5.F_PMAF_PURCHASESN=t0.F_PMAF_PURCHASESN
            ) t0 GROUP BY F_PMAF_PURCHASESN
            ) e1 ON e1.F_PMAF_PURCHASESN=e0.F_PMAF_PURCHASESNID
            LEFT JOIN(
            SELECT
            SUM(t0.FREALQTY) AS OUTSTOCKQTY,
            t0.F_PMAF_PURCHASESN,
            SUM(t2.F_PMAF_CUSTCOMMISSION*t0.FREALQTY/t2.FREALQTY) AS CUSTCOMMISSION,
            SUM(t2.F_PMAF_SALECOMMISSION*t0.FREALQTY/t2.FREALQTY) AS SALECOMMISSION,
            SUM(t2.F_PMAF_TRANAMOUNT*t0.FREALQTY/t2.FREALQTY) AS TRANAMOUNT
            FROM T_SAL_OUTSTOCKENTRY t0
            LEFT JOIN 
            (SELECT
            t0.FID,
            t0.F_PMAF_CUSTCOMMISSION,
            t0.F_PMAF_SALECOMMISSION,
            t0.F_PMAF_TRANAMOUNT,
            t1.FREALQTY
            FROM T_SAL_OUTSTOCK t0
            LEFT JOIN
            (SELECT
            FID,
            SUM(FREALQTY) AS FREALQTY
            FROM T_SAL_OUTSTOCKENTRY
            GROUP BY FID) t1 ON t1.FID=t0.FID) t2 ON t2.FID=t0.FID
            GROUP BY t0.F_PMAF_PURCHASESN) e2 ON e2.F_PMAF_PURCHASESN=e0.F_PMAF_PURCHASESNID;
            DROP TABLE #TEMP0;
            DROP TABLE #TEMP1;
            ", tableName);
            DBUtils.ExecuteDynamicObject(Context, sqlStr);
        }

        /// <summary>
        /// 设置汇总列
        /// </summary>
        /// <param name="filter"></param>
        /// <returns></returns>
        public override List<SummaryField> GetSummaryColumnInfo(IRptParams filter)
        {
            var result = base.GetSummaryColumnInfo(filter);
            result.Add(new SummaryField("PURQTY", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("FALLAMOUNT", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("SALESELFQTY", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("SALESELFAMOUNT", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("SALESELFPROFIT", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("SALEOTHERQTY", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("ADDAMOUNT", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("SALEOTHERAMOUNT", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("SALEOTHERADDPROFIT", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("SALEOTHERPROFIT", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("UNSALEQTY", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("OUTSTOCKQTY", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("CUSTCOMMISSION", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("SALECOMMISSION", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("TRANAMOUNT", BOSEnums.Enu_SummaryType.SUM));
            result.Add(new SummaryField("UNSALEAMOUNT", BOSEnums.Enu_SummaryType.SUM));
            return result;
        }

        /// <summary>
        /// 日期范围
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        private string GetDateZone(object startDate, object endDate)
        {
            StringBuilder sb = new StringBuilder();
            if (startDate != null)
            {
                sb.Append(startDate.ToString().Substring(0, 10));
            }
            else
            {
                sb.Append("--");
            }
            if (endDate != null)
            {
                sb.Append("至").Append(endDate.ToString().Substring(0, 10));
            }
            else
            {
                sb.Append("至--");
            }
            return sb.ToString();
        }


        private string GetPurchasers(DynamicObjectCollection purchasers)
        {
            StringBuilder sb = new StringBuilder();
            if (purchasers != null)
            {
                int i = 0;
                foreach (DynamicObject obj in purchasers)
                {
                    DynamicObject purchaser = (DynamicObject)obj["F_PMAF_Purchasers"];
                    string purchaserStr = purchaser["Name"].ToString();
                    sb.Append(purchaserStr);
                    if(i< purchasers.Count - 1)
                    {
                        sb.Append(";");
                    }
                    i++;
                }
            }
            return sb.ToString();
        }

        private string GetMaterials(DynamicObject startMaterial, DynamicObject endMaterial)
        {
            StringBuilder sb = new StringBuilder();
            if(startMaterial != null && endMaterial != null)
            {

            }
            return "";
        }
    }
}
